--================================================
-- Create a function to make NEXTID
--================================================
USE DAILY
GO

IF OBJECT_ID (N'dbo.FN_NextID') IS NOT NULL
	DROP FUNCTION dbo.FN_NextID
GO

CREATE FUNCTION dbo.FN_NextID (@LastId varchar(10), @Prefix varchar(10), @Size int)
	RETURNS varchar(10)
AS
	BEGIN
		IF(ISNULL(@Lastid, '') = '')
			SET @Lastid = @Prefix + REPLICATE (0, @Size - LEN(@Prefix)) 
		DECLARE @Num_NextId int
		DECLARE @Nextid varchar(10)
		SET @LastId = LTRIM(RTRIM(@LastId))
		SET @Num_NextId = REPLACE(@LastId, LEFT(@LastId, LEN(@Prefix)),'') + 1
		SET @Size = @Size - LEN(@Prefix)
		SET @Nextid = @Prefix + REPLICATE (0,@Size - LEN(@Prefix)) 
		SET @Nextid = @Prefix + RIGHT(REPLICATE(0, @size) + CONVERT (VARCHAR(MAX), @Num_NextId), @Size)
		RETURN @Nextid
	END;
GO

--================================================
-- Create a store procedure to get TYLECHIETKHAU
--================================================
USE DAILY
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'PROC_TLCHIETKHAU' 
)
   DROP PROCEDURE dbo.PROC_TLCHIETKHAU
GO

CREATE PROCEDURE dbo.PROC_TLCHIETKHAU @MaMatHang char(10), 
	@SoLuong smallint,
	@ChietKhau float OUTPUT
AS
	SELECT TOP 1 @ChietKhau = TLChietKhau
		FROM dbo.CHIETKHAU
		WHERE MaMatHang = @MaMatHang AND @SoLuong > SLToiThieu
		ORDER BY SLToiThieu DESC
	IF((@ChietKhau IS NULL)) 
	BEGIN
		SET @ChietKhau = 0
	END
GO

--================================================
-- Create a store procedure to get GIANIEMYET
--================================================
USE DAILY
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'PROC_GIANIEMYET' 
)
   DROP PROCEDURE dbo.PROC_GIANIEMYET
GO

CREATE PROCEDURE dbo.PROC_GIANIEMYET @MaMatHang char(10), 
	@Gia int OUTPUT
AS
	SELECT TOP 1 @Gia = Gia 
		FROM dbo.GIANIEMYET
		WHERE MaMatHang = @MaMatHang
		ORDER BY NgayApDung DESC, RIGHT(MaGia, 8) DESC
GO

--================================================
-- Create a store procedure to get LUONGNV
--================================================
USE DAILY
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'PROC_LUONGNV' 
)
   DROP PROCEDURE dbo.PROC_LUONGNV
GO

CREATE PROCEDURE dbo.PROC_LUONGNV @MaNV char(10), 
	@Luong int OUTPUT
AS
	SELECT @Luong = l.LuongCoBan 
		FROM dbo.NHANVIEN n INNER JOIN dbo.LOAINHANVIEN l 
		ON n.MaLoaiNV = l.MaLoai
GO

--================================================
-- Create a store procedure to get SOLUONGTON
--================================================
USE DAILY
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'PROC_SOLUONGTON' 
)
   DROP PROCEDURE dbo.PROC_SOLUONGTON
GO

CREATE PROCEDURE dbo.PROC_SOLUONGTON @MaMH char(10), 
	@SoLuong int OUTPUT
AS
	SELECT @SoLuong = SUM(SoLuongTon)
		FROM dbo.CTMATHANG 
		WHERE MaMatHang = @MaMH AND TrangThai = 1
GO
